package com.aixin.csopuser.repository;

import com.aixin.csopuser.domain.CustomerServiceConsume;
import com.aixin.csopuser.domain.dto.SupplierCustomerDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

public interface CustomerServiceConsumeRepository extends JpaRepository<CustomerServiceConsume,Long>, JpaSpecificationExecutor<CustomerServiceConsume> {

    @Query(value = "select new com.aixin.csopuser.domain.dto.SupplierCustomerDTO(ai.channelName, cu.customerID, cu.custName, " +
            "cu.gender, cu.certID, cu.phoneNumber, ce.policyNumber, sd.serviceName, csc.usedCount, " +
            "ce.equityID, ce.custLevelID, sd.serviceID, ce.effStrTime, csc.lastUseDate, cu.custStatus) " +
            "from  CustomerEquity ce join CustomerDomain cu on ce.customerID=cu.customerID " +
            "join ServiceLevelDef sld on sld.equityID=ce.equityID and sld.equityLevelID=ce.custLevelID " +
            "join ServiceDef sd on sd.serviceID=sld.serviceID " +
            "left join CustomerServiceConsume csc on csc.customerID=ce.customerID and csc.serviceID=sld.serviceID " +
            "left join AgentInfo ai on cu.agentCode=ai.agentCode " +
            "where 1=1 " +
            "and ((:customerName is null) or (cu.custName like concat('%', :customerName, '%'))) " +
            "and ((:identity is null) or (cu.certID like concat('%', :identity, '%'))) " +
            "and ((:supplierID is null) or (sd.supplierID=:supplierID)) " +
            "and ((:serviceID is null) or (sld.serviceID=:serviceID)) " +
            "and ((:statusUnUse is null) or (csc.usedCount is null)) " +
            "and ((:statusUsed is null) or (csc.usedCount>0))")
    Page<SupplierCustomerDTO> query(@Param("customerName") String customerName,
                                    @Param("identity") String identity,
                                    @Param("supplierID") String supplierID,
                                    @Param("serviceID") String serviceID,
                                    @Param("statusUnUse") String statusUnUse,
                                    @Param("statusUsed") String statusUsed,
                                    Pageable pageable);

    Optional<CustomerServiceConsume> findByCustomerIDAndServiceID(String customerID, String serviceID);
}
